Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 006 / expcalc2.arc / CALC2.DOC < prev next >

Wrap

Text File | 1986-09-23 | 90.3 KB | 3,301 lines

- FIX ____________________________________________________________________ FIX Function Purpose: Converts a number or formula to an integer, and returns the resulting value to the current cell or formula. Does not return the next lower number when negative, as does the INT function. Format: FIX(-2.5) Returns -2 FIX(A1) If A1 contains -2.5, returns -2. FIX(A1*2/A5) Resolves the formula, then returns the fixed decimal portion of the result Remarks: FIX(x) can be entered into a cell, or the FIX function can be used in a formula. All digits to the right of the decimal are removed, and the ones to the left of the decimal become the returned value. This differs from the INT function, which returns the next lower negative number. Example: Enter the following formulas into the specified cells. The resulting values are shown: Cell Formula Result A1: FIX(23.999) 23 A2: FIX(-1.567) -1 A3: INT(23.999) 23 A4: INT(-1.567) -2 51 - FORMAT ____________________________________________________________________ FORMAT Command (/F) Purpose: Sets the formatting options for a cell, a range of cells or the entire spreadsheet. Prompts: Enter /F and you will be prompted: Enter range to be formatted (or ALL). Enter a single cell, a range of cells (which may be a block), or ALL. To format only the current cell, press ENTER or comma. The next prompt is: Decimals Width Justify Commas $ ( % Protect Unprotect Hide Bargraph Zero-blank Enter D, W, J, C, $, (, %, P, U, H, B or Z to indicate which type of formatting you want to do. The next prompt depends on which option you select: Decimals Number of decimal places (0 to 12), Floating, Scientific. The number of places to the right of the decimal can be changed using this command. This number of decimals is used on the screen display, on printed output, and in computations. "Floating" works like the F setting on a calculator. All significant decimals to the right of the decimal are used in computations. If the column is wide enough, all significant decimals are displayed and printed. If the column is not wide enough, CALC drops enough decimals on the right so the number fits in the column. For example, if the formula 2000/3 is entered in a column that is ten characters wide, the number 666.666667 is displayed. If the column is narrowed to five characters, 52 - FORMAT the number 666.7 is displayed. Reducing the column width to four or three characters displays the number as 667 (with no decimal point). If the column width is narrowed to two, the << symbols print, indicating the number will not fit in the cell. "Scientific" format displays the number in exponential form, similar to scientific notation. The mantissa and exponent are separated by the letter E. For example, 3E-5 is the same as .00003 and -3E+10 is the same as -30,000,000,000. Width Enter column width (0 to 75). The width of a column or range of columns can be changed using this command. A column width of zero causes the column to be "hidden", allowing data to be stored without being seen. Justify Right-justify, Left-justify or Center. Specify "L" to have numbers shifted to the left side of the column. Specify "R" to have text shifted to the right side of the column. "C" centers the cell's contents. CALC defaults to right-justify for numbers and left- justify for text unless overridden by this command. Commas Commas between thousands (Y or N). To have 12345.00 print as 12,345.00 specify "Y" to this prompt. To remove the commas from a number, specify "N". For example, if the number is a year such as 1985 you would not want it to print as 1,985. $ Print leading dollar sign ($) on numbers (Y or N). If you specify Y, a dollar sign will be printed immediately to the left of the most significant digit. 53 - FORMAT ( Enclose negative numbers in parentheses (Y or N). If Y is specified, a negative number will have parentheses around it, rather than a minus sign. To switch back to minus sign, specify N. Note: When this format option is specified, positive numbers have one blank space to the right. This is necessary so a column of positive and negative numbers line up properly. % Print a percent sign (%) to the right of numbers (Y or N). If Y is specified, a percent sign is printed on the right of the number. This option does not affect computation. Protect F = protect formulas, V = protect values, T = protect text, N = protect non-blank cells, A = protect all cells in range When a cell is protected, nothing can be entered into it. This keeps formulas and headings from accidentally being destroyed by overtyping. When a cell is protected, the word PROTECTED appears on the message line when the cursor moves to that cell. Also, if you have configured CALC accordingly, protected fields can be displayed in a different color than the display area (or in inverse video on a monochrome monitor). Once a cell is protected, it can be unprotected using the Unprotect format option, discussed next. From the message, you can see there are several options for protecting ranges of cells. The Protect Option has no global setting. All cells are unprotected at startup, and if you protect ALL, every cell in the spreadsheet is individually protected. Unprotect F=unprotect formulas, V=unprotect values, T=unprotect text, N=unprotect non-blank cells, A=unprotect all cells in range When a cell has been protected, nothing can be entered into it. To remove the protection so the cell's contents can be changed, use the Unprotect option. 54 - FORMAT From the message, you can see there are several options for unprotecting ranges of cells. The Unprotect Option has no global setting. All cells are unprotected at startup. Hide P=hide cells on printer only, S=hide cells on screen only, B=hide cells on printer and screen, N=Don't hide cells. A hidden cell is one which has something in it, but its contents are not displayed on reports and/or the screen. Sometimes it is useful to hide some cells such as those containing lookup tables. Sometimes there is confidential data used in calculations which should not be included on hard copy reports. Once a cell is hidden, you can still see its contents by either moving the cursor to the cell and looking at the message line, or by using the /GF command to display formulas on the screen. /GF ignores the hidden cell flag. There are several options for hiding/unhiding ranges of cells. The Hide Option has no global setting. All cells are unhidden at startup. Bargraph Print numbers in bar graph format 1, 2 or 3 (N=no graph). If a cell contains a value or formula, the result will be displayed as a string of bar graph characters. By entering a 1, 2 or 3, up to three different bar graph patterns/colors can be displayed. If the bar graph exceeds the width of the cell, the last character displays a > character, indicating the bar continues beyond the column. If the bar graph is in a right-justified cell, the bar graph is right-justified, and the > sign becomes a < sign on the left. When a bar graph cell is printed, one of three characters is printed. CALC's default characters for printed bar graphs are ****, //// and ]]]] , which are not nearly as attractive as the patterns on the screen. If your printer 55 - FORMAT has a graphics character set, it is possible to configure CALC to print the same patterns as appear on the screen, or others if you prefer. See the section titled Customizing CALC for information on changing printed bar graph characters, screen bar graph characters and screen bar graph colors. Zero-blank Display zeros as blanks (spaces) (Y or N). If this option has been set for a cell, and the cell contains a value or formula which resolves to zero, the cell will appear empty. At startup, the default is to display zeros (0, .0, .00, etc.) in all cells. CALC has always had a global option to not display zeros. The /G,Z command still exists, and is effectively the same as /F,ALL,Z. Remarks: Each of the formatting options which have responses of "Y" or "N" will also accept a response of ENTER. If you press the ENTER key instead of Y or N, the formatting options for the specified cell(s) clear the format option, so the cell's format reverts to the default (global) setting. After responding to one of the formatting options, CALC repeats the prior prompt, allowing you to specify more than one formatting option for a cell or range. A cell can be formatted before it has data put in it, and the format options take effect as soon as data is put in it. However, empty cells' formats are not saved when a spreadsheet is /Saved. When individual cells are formatted, two characters of memory are used per cell. But when ALL is specified, a "global default" code is changed in CALC. Specifying ALL uses none of the spreadsheet memory. Specifying a range of cells causes two bytes of memory to be used for 56 - FORMAT every cell in the range. So for example, this statement: /F A1:Z99,D,3 uses 5K of memory, while this statement: /F ALL,D,3 uses no memory at all. So on large spreadsheets it is a good idea to determine the most common default values first, and set them with the "ALL" global format option. Then the cells that vary from the default can be changed individually. This not only saves memory, it usually saves typing as well. The global format defaults are as follows: Decimals: 2 Width: 11 (all columns) Justify: Left for text, Right for numbers Commas: Yes Protected: No Bargraph: No Hidden: No Zero-blank: No $, (, %: No These global defaults can be permanently changed by specifying ALL, then using the /Configure,Save command to permanently save them in CALC.PRO. See the section titled Customizing CALC for more information. Example: EXAMPLE #1: A series of year numbers have been entered into column B, starting with 1983. The problem is that the standard defaults cause the years to print as 1,983.00 instead of 1983. Use the /F command first to turn off the decimals, then to turn off the commas: First enter: /F B1:B50,D,0 then enter: C,N 57 - FORMAT The years will now print as four-digit numbers without commas and decimals. To also left-justify the years, and their heading which is a text field: "Years", within column B, enter: /F B1:B50,J,L EXAMPLE #2: We want to prepare a spreadsheet to produce the following report: Period ------- Sales ------- 1/85 $50,000 ***** 2/85 $59,000 ****** 3/85 $42,400 **** 4/85 $92,800 ********* 5/85 $121,000 ************ Begin by entering the numbers and text. This is what they look like before formatting: A B C D E 1 Period ------- Sales ------- 2 1.00 / 85.00 50,000.00 5.00 3 2.00 / 85.00 59,000.00 6.00 4 3.00 / 85.00 42,400.00 4.00 5 4.00 / 85.00 92,800.00 9.00 6 5.00 / 85.00 121,000.00 12.00 Note that we could have entered the month/year as text fields. But by making them numeric we can use the Replicate command to enter them, saving considerable typing on large spreadsheets. (See /Replicate for an example of how to use it on columns A and B above.) To get the formatting right on the spreadsheet above, start by specifying a global default of no decimals: /F ALL D 0 (ENTER) 58 - FORMAT Next, change the column widths. Columns A and C need to be two characters wide. Column B needs to be one character wide. Column D, no change, and column E should be wide enough to handle a lengthy graph, say forty characters: /F A1:C20 W 2 (ENTER) (ENTER) /F B1:B20 W 1 (ENTER) (ENTER) /F E1:E20 W 40 (ENTER) Next specify Bargraph format for column E. Notice that you didn't hit ENTER twice on the previous command, so you need not retype /F E1:E20. Just type: B 1 Y (ENTER) (ENTER) And finally, put floating dollar signs on column D: /F D1:D20 $ Y (ENTER) (ENTER) On the screen, the bargraphs do not appear as asterisks; they are shaded bars. When printed, they will appear as asterisks. If your printer has graphic characters, it is possible to print special characters like the ones on the screen. See the section titled Customizing CALC for more information. One suggestion on the graphic display in column E: use formulas based on column D. For example, in cell E2, instead of entering "5", enter "D2/10000". In that way, if column D's figures are changed, the graphs are immediately updated. Also, it makes the graph column easy to replicate. 59 - GLOBAL ____________________________________________________________________ GLOBAL Command (/G) Purpose: Sets various system options of CALC, including border display, row/column calculation sequence, automatic recalculation, and printing zeros as blanks. Prompts: Enter /G and you will be prompted: Border, Row, Column, Manual, Auto, Formula, Zero-blank, Smart-cursor To select a global option, enter the first letter of the option. It is not necessary to press ENTER. Remarks: Border The BORDER option turns the screen borders on and off. If your screen has borders on the top and left edge, use this option to turn them off. To turn them back on, use this option a second time. Note: this option has no effect on printed reports. There is a special option in the /P command for printing borders. Row and Column The ROW and COLUMN options indicate whether calculation should proceed row-by-row, or column-by-column. Each time a calculation is done, you can watch the cursor "ripple" down the screen, recalculating each of the formulas in your spreadsheet. In many cases, a formula will refer to a cell that contains another formula. When this happens, the formulas must be evaluated in the proper sequence, or you will not get the answer you were expecting. Most spreadsheets are designed for row-by-row calculation, so ROW is the default at startup time. If you design a spreadsheet that requires calculation to be done down the 60 - GLOBAL columns instead of across the rows, specify the COLUMN option. If your spreadsheet has forward references (references to formulas which have not been recalculated yet), and neither ROW or COLUMN will work properly, you may need to use iterative calculation. See the ITERATIONS option in the section titled "Customizing CALC" for more information. Warning: Be careful to not create spreadsheets with "circular references": two formulas refer to each other, or "A" refers to "B" refers to "C" refers to "A". When you suspect a circular reference, press ! to recalculate. If one or more of the numbers on your spreadsheet keep changing each time you recalculate, your spreadsheet probably has a circular reference, and may need to be restructured to eliminate the problem. Manual and Auto The MANUAL and AUTO options tell CALC whether to automatically recalculate each time a number or formula is entered, or whether to wait and not calculate until the ! is pressed. At startup, the AUTO option is set. As numbers and formulas are entered, the message "CALCULATING" will appear, and the cursor will "ripple" down the screen each time before the next field can be entered. As the spreadsheet gets larger, this pause can become annoying. To turn off automatic recalculation, select the MANUAL option. Formula The FORMULA option tells CALC to display cells' contents on the screen instead of the cells' values. For example, if cell A5 contains A1+A1 and displays 12.34 on the screen, /GF causes A1+A1 to be displayed on the screen, in the cell. Typing /GF a second time returns the display to its normal mode. 61 - GLOBAL In Formula mode, text is displayed left-justified in its cell, with a leading quotation mark. Values and formulas are displayed exactly as they were entered. Any text, formulas or values which do not fit in the column overlap into adjacent empty columns on the right. In Formula mode, Protected fields have a leading "P" on the left in inverse video. Hidden fields' contents are not hidden in Formula mode. Zero-Blank The ZERO-BLANK option tells CALC to display all cells whose contents are zero as blank cells. At startup, the default is to display zeros (0, .0, .00, etc.) in the cell. Select this option to display them as blanks. To restore the original default of displaying zeros as 0, .0, etc. use /Global, Zero-blank a second time. See also /Format, Zero-blank. Cursor The Smart-cursor option changes the way the ENTER key works. If the Smart Cursor has been turned on, CALC "remembers" the direction the arrow key moved previously. When the ENTER key is pressed, the cell cursor automatically moves in the remembered direction. When the Smart Cursor is turned on, an arrow appears in the lower left corner of the screen, indicating the direction the cursor will move next. When ENTER is pressed, the cell cursor moves in that direction. Accountants and others who are accustomed to ten-key entry of columns of numbers will find Smart Cursor especially useful. To enter a column of numbers with Smart Cursor, enter the first number and move the cursor downward. Then type /GS to turn on the Smart Cursor. A tiny down-arrow appears in the lower-left corner of the screen. Put your ten-key pad in numeric lock, enter the next number on it ten-key style, and then press the ENTER key with your forefinger. The number is entered and the 62 - GLOBAL cursor automatically advances downward. For experienced ten-key operators, this is a very fast way to enter numbers. Example: A spreadsheet is getting large, and every time a number is typed in, a recalculation is done which takes several seconds to complete. Turn off automatic recalculation with this command: /G M Later when recalculation is desired, you can type ! to force recalculation. If you get to the point of fine- tuning the spreadsheet and would like to have recalculation turned back on, enter: /G A 63 - IF ____________________________________________________________________ IF Function Purpose: Evaluates an expression as true or false, and returns the "then" argument if true, or the "else" argument if false. Format: IF(A1=5,1,0) If A1 contains a 5, a 1 is returned. Otherwise a 0 is returned. IF(A1>B5,D3,99) If the number in A1 is greater than the number in B5, the contents of D3 are returned. Otherwise the number 99 is returned. Alternate form: IF A1=5 THEN 1 ELSE 0 (Same as first example above) IF A1 > B5 THEN D3 ELSE 99 (Same as second example) Remarks: The three operands of the IF function can best be described as follows: IF(this expression is true,then return this expression,otherwise return this expression) If the first operand contains two expressions separated by a relational operator, the expressions are resolved, then compared. If the resulting comparison is true, the second operand is used. If the comparison is false, the 64 - IF third operand is used. Valid relational operators are: Operator Meaning Example = Equal A1 = 0 <> or >< Not equal A1 <> B1 < Less than A1 < B1+3 > Greater than A1 > .0987 <= or =< Less than or equal to .987 <= A1 >= or => Greater than or B1*3 >= 5-A1 equal to If the first operand contains no relational operator, it is resolved and compared to zero. If it is not zero, the second operand is used. If it is zero, the third operand is used. For example, enter IF(5,3,2) in a cell, and the number 3 will appear, since 5 is non-zero. Multiple comparisons may be done in the first operand, using Boolean (logical) operators. For example, if a number being tested can be either 1 or 2, the IF function could be written like this: IF A1=1 OR A1=2 THEN ... ELSE ... The second and third operands of the IF function may be any valid value, cell reference or formula. They may not be text. However, text may be displayed by putting it in another cell and referring to that cell in an IF operand. For example, if A5 contains the text "Out of stock", and at A8 we entered IF(1=1,A5) then cell A8 would display the text "Out of stock". Another example of this feature is shown below, in the example section. There is an alternate form of the IF function which some people find easier to read. The parentheses are optional, and the words THEN and ELSE can be used instead of commas. THEN and ELSE must have at least one space to 65 - IF the left and right. Spaces can be used freely in any of the operands, except that cell references cannot have spaces in them; i.e. A11 can't be A 11. For example: IF A1 = 5 THEN 1 ELSE 0 IF A1 > B5 THEN D3 The IF function may be used in formulas. For example, this is a valid expression: 1000+A1*IF(B1,C1,0) The IF function may also be nested. For example, this is a valid expression: IF(A1>0,B5,IF(A1=0,B6,B7)) The expression tests A1 to see if it is greater than zero. If it is, the contents of B5 are returned. But if the number in A1 is not greater than zero, another test is done. Is it equal to zero? If so, return B6, otherwise return B7. An IF statement may be nested in the second and third operands, but may not appear in the first operand of another IF statement. The nesting of IF statements can be as many levels as you want, but the real restriction is the size of the formula that can be entered (currently 74 characters). By breaking them up into multiple cells that refer to one another, very complex relational expressions can be generated. Keep in mind, however, that evaluating complex expressions can slow down recalculation considerably. Example: EXAMPLE #1: A salesman's commission is 12% of gross sales, but the salesman is always guaranteed to make at least a minimum of $1500. Gross sales are in cell B5. Compute his commission in C5, using the IF function. Move 66 - IF the cursor to C5, and enter: IF(B5*.12>1500,B5*.12,1500) If gross sales times .12 are greater than $1500, pay the salesman that amount. But if gross sales times .12 are less than or equal to $1500, pay him a flat $1500. If the spreadsheet listed several salesmen down the columns, this formula could be copied to all of them, using the /Replicate command. EXAMPLE #2: A spreadsheet contains the payroll for an employee. The pay periods are numbered 1 through 24, in cells A3 through A26. The columns look like this: A B C D 1 Pay Gross YTD 2 Period Wage Gross FICA The employee's gross pay for each period is in column B. Year-to-date gross pay is in column C, which is simply the running total of column B. FICA for each period is in column D. The FICA computation can normally be done with a simple multiplication: gross wage times .067. But if the employee makes more than $37,800 his FICA deduction should stop. Assume that all the other numbers and formulas have been filled in for the 24 pay periods, and use the IF function to compute FICA in D3 through D26. In D3, put the following formula: IF(C3<37800,B3,IF(C3-B3>37800,0,37800-C3+B3))*.067 The formula says: If YTD gross wage is less than 37800, then use the current gross wage, times .067. But if last period's YTD gross (C3-B3) was over 37800, then the ceiling has been reached, so FICA is zero. Finally, if we are in the period where only part of the wage is subject 67 - IF to FICA, compute that amount (37800-C3+B3) and multiply it times .067. Now use the /Replicate command to copy D3 to cells D4 through D26. When it asks Adjust - Y/N? (or A) , reply A. EXAMPLE #3: A spreadsheet contains student names in A5 through A30, and test scores in B5 through B30. We want to show each student's grade in column C, using the following table: Score of 90 to 100 - grade is A Score of 80 to 89 - B Score of 55 to 79 - C Score of 40 to 54 - D Score of 0 to 39 - F Out to the right of the spreadsheet, in cells G1 to G5, enter the text data: G1: A G2: B G3: C G4: D G5: F At cell C5, enter this expression: IF(B5>89,G1,IF(B5>79,G2,IF(B5>54,G3,IF(B5>39,G4,G5)))) Then use the /Replicate command to copy the cell to C6 through C30. When /R asks Adjust - Y/N? (or A) reply Y to the cells in column B, and N to the cells in column G, which are constant data. The table in column G can be hidden from view on a printed report or on the screen by setting the column width to zero, or by using the /Format, Hide command. 68 - INSERT ____________________________________________________________________ INSERT Command (/I) Purpose: Inserts a row or column in the spreadsheet. Prompts: Enter /I and you will be asked: Insert Row or Column? to which you reply either R or C. The next prompt is: Enter row number. New row will be inserted above it. or Enter Column letter. New column will be inserted to left. The default, if ENTER is pressed, is to use the row number or column letter of the current cell (the one where the cell cursor is currently located). Remarks: If a column is inserted, all the columns to the right of it shift right. Likewise, if a row is inserted, all the rows below it shift down to make room. Any formulas referring to the shifted cells have their coordinates adjusted so they point at the same data. When a column is inserted, the column widths are shifted to the right with their data. The new column retains the same column width it originally had. Another way to insert a row is to move the cell cursor one row below where the new row is desired and press CONTROL-N. Example: A spreadsheet lists all customers and their aged accounts receivable. A new customer needs to be added between rows 21 and 22. To insert the row, either move the cursor to row 22 and enter: /I R (ENTER) 69 - INSERT or if you are not at row 22, enter: /I R 22 (ENTER) Row 22 becomes blank, and the data that was on row 22 moves to 23, 23 moves to 24, etc. 70 - INT ____________________________________________________________________ INT Function Purpose: Converts a number or formula to an integer, and returns the resulting value to the current cell or formula. Returns the next lower number when negative. (See also FIX function.) Format: INT(5.1) Returns 5 INT(A1) If A1 contains -2.5, returns -3 INT(A1*2/A5) Resolves the formula, then returns the fixed decimal portion of the result Remarks: INT(x) can be entered into a cell, or the INT function can be used in a formula. All digits to the right of the decimal are removed. If the number is negative, INT returns the next lower negative number. This differs from the FIX function, which returns the integer portion of a number, regardless of sign. Example: Enter the following formulas into the specified cells. The resulting values are shown: Cell Formula Result A1: INT(23.999) 23 A2: INT(-1.567) -2 A3: FIX(23.999) 23 A4: FIX(-1.567) -1 71 - KEYWORD ____________________________________________________________________ KEYWORD Function Purpose: Displays date, time and page number information. The format is user-defined. Format: KEYWORD(keywords) KEYWORD(DATE) Displays today's date in the cell Example: October 9, 1985 KEYWORD(TIME) Displays the current time of day in the cell: 8:30:10 PM KEYWORD(DA-MO-YR HO:MI page #) Displays the day number, month number and year separated by dashes, then the hour and minute separated by a colon, then the word "page" followed by the currently printing page number. On the screen it looks like: 9-10-85 20:30 page 1 Remarks: KEYWORD can be entered into a cell with any length of keyword argument enclosed in parentheses. KEYWORD cannot be used in an IF statement or formula. The text string enclosed in parentheses is scanned for several valid keywords (listed below) and substitutions are made. Characters which are not keywords are left in the string. In the example above, the keywords used are DA, MO, YR, HO, MI and #. The dashes, colon, spaces, and the word "page" are not keywords, so they remain where they are in the text string. The keyword function is handled internally as a formula, but its contents are displayed as text. Text is left- 72 - KEYWORD justified unless overridden by /Format. Text will overlap into an adjacent empty cell. The date and time displayed are MSDOS system date and time. If your computer does not have a battery backup clock/calendar, you will need to enter the system date and time using the DOS commands DATE and TIME each time you turn on your computer. Keyword Sample Result Explanation DATE or May 9, 1985 Month name, day number and year number. TODAY Same as KEYWORD(Month DA, YEAR) TIME 8:30:10 PM Current hour, minute and second. The time is updated each time a recalculation is done, or when the cursor moves to the cell. Month June Current month name MONTH JUNE Current month name, all capital letters Mon Jun First three letters of month name MON JUN First three letters of month name, all caps MO 9 Month number. If less than 10, only one digit MZ 09 Month number. If less than 10, leading zero. DA 3 Day number. If less than 10, only one digit DZ 03 Day number. If less than 10, leading zero. YEAR 1985 Four digit year number. YE 85 Two digit year number. YR and yr also valid. HO 8 Current hour. No leading zero. Displayed in military time if no AM/PM keyword is in 73 - KEYWORD the string (see below). If AM/PM appears, display will be twelve-hour clock, and the AM/PM string will be changed accordingly. HZ 08 Current hour. Same as HO, but if less than ten, leading zero is printed. MI 30 Current minute. Two digits. SE 10 Current second. Two digits. AM, PM AM Variants: AM PM am pm A.M. P.M. a.m. p.m. If one of these strings appear, time is displayed in twelve-hour format. # 1 When printing, displays current page number. 74 - LOAD ____________________________________________________________________ LOAD Command (/L) Purpose: Loads a spreadsheet file into memory so it can be altered or printed. /Load can also be used to read DIF files, ASCII Mail-merge files, File Express databases, or PC-File databases. There is also a Consolidate option, which allows spreadsheets with the same format to be consolidated. Prompts: Enter /L. The message Loading program. stand by . . . appears for a moment, as the Load/Save program is brought into computer memory. Then the prompt appears: Enter the file name. (Enter A:, B:, etc. for directory.) At this point, you have two choices: display a disk directory or load a file. If you press ENTER, the directory of the currently logged disk drive is displayed. By entering A:, B:, etc. you can display disk directories on other drives. If instead you enter a file name, CALC will load that file. The file must be one of the following: . A CALC spreadsheet file created with the /Save command . A DIF format file (data interchange format) with an extension of .DIF . A sequential ASCII file with fields comma-delimited, text in quotation marks, records ending with carriage return/line feed, and a file extension of .WS . A File Express or PC-File database. Specify the database name with an extension of .HDR (the header file for the 75 - LOAD database) . A CALC spreadsheet file created with PC-File's Export command or a BASIC program The file name can be any valid MS-DOS file name; i.e. eight characters or less, optional extension, optional drive designation. DIF files must have an extension of .DIF; comma-delimited ASCII files must have an extension of .WS; databases must have an extension of .HDR. Some examples of valid file names are: LOAN1 CALC file B:WORKSHT5.OLD CALC file on drive B: TEST.DIF DIF format file ADDRESS.WS ASCII comma-delimited file CUSTOMER.HDR File Express database Spreadsheet files If the file being loaded is a CALC spreadsheet file, the next prompt is: All, Part or Consolidate? If you press "A" (All) the entire spreadsheet is loaded. If you press "P" (Part) a prompt is given to enter the range of cells; only those cells are loaded into the spreadsheet area. On either of these options, if the spreadsheet area has data in it, a warning message is given and the area can be cleared by pressing Z, or left intact by pressing ENTER. If you select "C" (Consolidate) only the cells with values are input from the spreadsheet files. But instead of replacing the value in the cell, it is added to the current value of the cell. Formulas and text are not read when Consolidate is selected. So if you have three 76 - LOAD spreadsheets to combine, load the first one normally, specifying All, then load the second and third specifying Consolidate. The formulas and text from the first one will remain untouched. When you are done, press ! and the formulas will recalculate using consolidated values. DIF, WS, HDR Files When loading a DIF, WS or HDR file, different prompts are given. First, you are asked: Enter the starting cell or cell range. The default is cell A1. To start the load at C11, enter C11. To confine the data loaded to three columns wide and fifty records long, starting at C11, enter C11:E60. The second prompt asks: Enter R to load by rows, C to load by columns. The default is R, since this will put one record on each line, which is the most common method. If you specify C, the file will be read into the spreadsheet area one record per column, effectively rotating it a quarter turn. DIF, HDR and WS files all have means of differentiating text from numbers. None of these file types accomodate formulas. The /Save command can save files in DIF and WS format, but they are not usually good formats for saving spreadsheets because formulas and formatting info are not saved. Use the CALC format for saving CALC spreadsheets. Remarks: When the file is opened, if it does not exist, or if it is not a file that can be read by CALC, an error message is given, and you are prompted to try again. As the file is loading, the cursor coordinate in the 77 - LOAD lower left corner of the screen displays the progress of the load. When the load is completed, the spreadsheet is displayed. CALC does not clear the spreadsheet area before loading a file. This allows two or more spreadsheets to be loaded and combined. However, if the spreadsheet area contains data, a warning message is given. You may press ENTER to keep the data in the spreadsheet area, or press Z to clear the area before loading. When loading a CALC spreadsheet, more than just the data in the cells is loaded. All global settings, printer settings, column widths and the current cell position are loaded as well. So if you save a spreadsheet at the end of the day, then reload it the next morning, everything will be back the way it was. To exit from /L without loading a file, press ESCAPE. If your computer has only one diskette drive, do not attempt to /Load or /Save to drive B:. CALC requires that the program diskette remain in the drive at all times. The message file and the file overlay program are both on drive A:, and are needed continually during the loading process. 78 - LOG ____________________________________________________________________ LOG Function Purpose: Computes the natural logarithm of a number, and returns the resulting value to the current cell or formula. Format: LOG(2.718282) Returns 1 LOG(A1) If A1 contains e, returns 1 LOG(A1*2/A5) Resolves the formula, then returns the natural log of the result Remarks: LOG(x) can be entered into a cell, or the LOG function can be used in a formula. The natural logarithm is the logarithm to the base e. Example: Enter the following formulas into the specified cells. The resulting values are shown: Cell Formula Result A1: LOG(2.718282) 1 A2: LOG(2.718282*2.718282) 2 A3: LOG(45/7) 1.86 79 - LOOKUP ____________________________________________________________________ LOOKUP Function Purpose: Searches a table of ascending numbers, finds the closest matching entry, and returns a value from an adjacent cell. Format: LOOKUP(A1,J1:J20) Searches J1:J20 comparing them to A1. First cell greater than A1 returns previous value in column K. LOOKUP(A1,J1:J20,3) Same as above, but returns the value in column M. Remarks: This function is useful for tax table lookups, price table lookups, etc. where the table is small enough to be entered as part of the spreadsheet. The first argument is the "search key". For a tax table lookup, it's taxable income. In a price table lookup, it's the part number to be found. The search key can be a number, or a formula, or a cell reference. The second argument is the "search table". It is a range of cells (two cell references separated by a colon), running down a column or across a row. These cells contain the table lookup values. In a tax table lookup, these cells contain the income thresholds for each tax bracket. In a price table lookup, these cells might contain part numbers. The LOOKUP function looks through the "search table" until it finds a number that is greater than the "search key". When it finds the number, it returns the value in the previous cell to the right (if the search table is a partial column) or the previous cell below (if the search 80 - LOOKUP table is a partial row). The values in the search table must be in ascending order, and no two cells in the search table can contain the same value. There is an optional third argument on the LOOKUP function, called "offset". Offset defaults to 1 if it is not present. Entering 2 in the third argument tells LOOKUP to go two columns to the right of (or two rows below) the search table to get its result. If offset is zero, the search table argument itself is returned. If offset is negative, LOOKUP moves to the other side of the search table for its result. Offset can be a number, or a formula, or a cell that contains a number. Example: EXAMPLE #1: A tax table set for LOOKUP might look like this: A B C 1 Over Tax + % 2 3 0 0 .00 4 2,300 0 .11 5 3,400 121 .12 6 4,400 241 .14 7 6,500 535 .15 8 8,500 835 .16 9 10,800 1,203 .18 10 12,900 1,581 .20 11 15,000 2,001 .23 The tax thresholds are listed in column A, the base tax in B and the percent to be applied to excess in C. Further down the spreadsheet we enter: 81 - LOOKUP A B C 15 Taxable Income: $9,000.00 16 17 Tax bracket: LOOKUP(C15,A3:A11,0) 18 Base tax amount: LOOKUP(C15,A3:A11,1) 19 % to apply to excess: LOOKUP(C15,A3:A11,2) 20 Excess tax amount: (C15-C17)*C19 21 22 Total tax: C18+C20 Which yields the following results: A B C 15 Taxable Income: $9,000.00 16 17 Tax threshold: $8,500.00 18 Base tax amount: $835.00 19 % to apply to excess: .16 20 Excess tax amount: $80.00 21 22 Total tax: $915.00 The example was broken up into pieces for demonstration purposes. More likely, the spreadsheet would simply contain a cell with "Total tax", using this formula: LOOKUP(C15,A3:A11)+(C15-LOOKUP(C15,A3:A11,0))*LOOKUP(C15,A3:A11,2) EXAMPLE #2: A lookup table is set up which contains a list of item numbers in column A, their description in item B, and their list price in column C: A B C 50 Part Description List Price 51 1001 Printer Stand 95.00 52 1002 Paper Tray 40.00 53 1003 Tractor Feed Option 125.00 54 1004 Ribbon Cartridge 9.95 55 (etc.) 82 - LOOKUP At the top of the spreadsheet is an invoice, as follows: A B C D E 1 INVOICE 2 3 Part Description List Price Qty Ext. 4 5 LOOKUP(A5,A51:A99) LOOKUP(A5,A51:A99,2) C5*D5 6 LOOKUP(A6,A51:A99) LOOKUP(A6,A51:A99,2) C6*D6 7 LOOKUP(A7,A51:A99) LOOKUP(A7,A51:A99,2) C7*D7 8 LOOKUP(A8,A51:A99) LOOKUP(A8,A51:A99,2) C8*D8 ... (etc.) 20 INVOICE TOTAL SUM(E5:E19) 21 SALES TAX E20*.078 22 AMOUNT DUE E20+E21 To produce an invoice, load the invoice spreadsheet from disk, enter the part numbers and quantities, then press ! to calculate. The descriptions and prices are looked up, the invoice is extended, sales tax is computed, and the invoice is totalled. When /Printing the invoice, specify a cell range of A1:E22, so the price table doesn't print. There is an alternate method for preparing invoices with CALC described in the examples of the NOPRINT function. 83 - LPI ____________________________________________________________________ LPI Function Purpose: Changes the line spacing on the printer to 6 or 8 lines per inch. Format: LPI(6) Changes to 6 lines per inch LPI(8) Changes to 8 lines per inch Remarks: When LPI(n) is entered into a cell, it displays as [LPI(n)] on the screen. When the cell is printed, control characters are sent to the printer which change the line spacing, then the cell is printed as if it were empty. LPI can be an operand in an IF, but cannot be used in a formula. If your printer is not an IBM, Epson, or Epson compatible, then CALC must be configured for your printer before using the LPI function. See the section titled "Customizing CALC" for more information. CALC defaults to six lines per inch, and 58 printed lines per page. When using LPI(8), you will probably want to increase the number of lines per page (one of the /Print prompts) to 75 or 80. Example: A report lists all 70 of a company's locations, and they won't all fit on one page with headings and totals. We want the report on a single sheet of paper, and it doesn't matter if the lines are "crunched" tightly together. At the top of the report, in any cell, type LPI(8) and press ENTER. At the bottom of the report, in any cell, type LPI(6) to reset your printer for future printing at six lines per inch. Now /Print the report, and specify a "page length" of 80. The report is printed on one page. 84 - MAX ____________________________________________________________________ MAX Function Purpose: Finds the largest number in a range of numbers and returns it to the current cell or formula. Format: MAX(A1:A20) Searches a column of numbers MAX(A1:E1) Searches a row of numbers MAX(A1:D20) Searches a block of numbers Remarks: MAX(m:n) can be entered into a cell, causing the largest number in the range to appear as the cell value. Or the MAX function can be used in a formula. The coordinate range specified in a MAX function may be down a column, such as MAX(A1:A20), it may be across a row, such as MAX(A1:E1), or it may be a block of cells (designated by the upper-left and lower-right coordinates), such as MAX(A1:D20). Negative numbers are considered smaller than zero; i.e. the MAX of 1, 0 and -5, is 1. If the specified range contains any empty cells, they are ignored. If the range contains any cells with text, ERROR is returned. Example: Column B has a column of monthly sales from B7 to B26. The highest monthly sales figure is to be shown in B28. Move the cell cursor to B28, and enter: MAX(B7:B26) After recalculation, B28 contains the highest number in the column. 85 - MIN ____________________________________________________________________ MIN Function Purpose: Finds the smallest number in a range of numbers and returns it to the current cell or formula. Format: MIN(A1:A20) Searches a column of numbers MIN(A1:E1) Searches a row of numbers MIN(A1:D20) Searches a block of numbers Remarks: MIN(m:n) can be entered into a cell, causing the smallest number in the range to appear as the cell value. Or the MIN function can be used in a formula. The coordinate range specified in a MIN function may be down a column, such as MIN(A1:A20), it may be across a row, such as MIN(A1:E1), or it may be a block of cells (designated by the upper-left and lower-right coordinates), such as MIN(A1:D20). Negative numbers are considered smaller than zero; i.e. the MIN of 1, 0 and -1, is -1. If the specified range contains any empty cells, they are ignored. If the range contains any cells with text, ERROR is returned. Example: Column B has a column of monthly sales from B7 to B26. The lowest monthly sales figure is to be shown in B27. Move the cell cursor to B27, and enter: MIN(B7:B26) After recalculation, B27 contains the lowest number in the column. 86 - NOLF ____________________________________________________________________ NOLF Function Purpose: Causes the current line to be printed with only a carriage return, and no line feed. On many printers, this allows two or more lines to be printed "on top of" one another. This is useful for boldfacing, underlining and overstriking. Format: NOLF Suppresses line feed when line is printed Remarks: NOLF can be entered into a cell, or can be the second or third operand in an IF function. It cannot be used in a formula. NOLF displays as [NOLF] on the screen. Regardless of which column NOLF is in, it takes effect at the time its line is printed. The cell with NOLF is treated as if it were empty when the print line is built, then when the line is printed, a carriage return is sent to the printer without a line feed character. On most printers, NOLF keeps the paper from advancing so the next line overprints the NOLF line. However, some brands of printers advance the paper anyway when a carriage return is received, so NOLF has no effect on those printers. Example: EXAMPLE #1: At the end of a report we want to underline the columns of numbers. In the past, we have always moved down one line and typed underscores, leaving a blank line above the underscores: 992.00 4.01 7,122.50 __________ __________ __________ TOTAL $4,305.00 $132.22 $92,122.50 87 - NOLF This is acceptable, but we would prefer the underscores to be immediately under the last line of numbers. So on the line immediately above the underscore line, we enter NOLF into any cell on that line. Now the line prints: 992.00 4.01 7,122.50 __________ __________ __________ TOTAL $4,305.00 $132.22 $92,122.50 EXAMPLE #2: On the same report described in example 1, we want the grand total line to print in "boldface"; i.e. darker than the rest of the report. Some printers may have a boldface font option, in which case the appropriate control codes could be typed into the "TOTAL text field. Most printers can print boldface by "overstriking" -- printing the same text two or three times, to make the characters darker. To boldface the total line, start by reproducing the line a second time, exactly as it appears. Use the /Replicate command to copy it to the line below it (with no adjustment). In any empty cell on the first total line, enter NOLF. When the first total line is printed, the printer returns the carriage but does not advance the paper. Then it prints the second total line over the first, making it darker. If a double-strike does not make the line dark enough, it may be necessary to print it three times, with NOLF in the first two lines. 88 - NOPRINT ____________________________________________________________________ NOPRINT Function Purpose: Causes the line where it appears to not be printed. Using NOPRINT and IF together allows selective printing. Format: NOPRINT Suppresses printing Remarks: NOPRINT can be entered into a cell, or can be the second or third operand in an IF function. It cannot be used in a formula. NOPRINT displays as [NOPRINT] on the screen. Any line with [NOPRINT] in one of its cells is not printed. Selective printing based on data values can be done by specifying NOPRINT in an IF function. For example: IF A5=0 THEN NOPRINT does not print if A5 is zero, and prints if A5 is non-zero. Example: A company has invoices which can include any of 80 line items. However, a typical invoice only has a few items on it. Every time we print an invoice, we don't want all 80 items printing; they take up two pages, and the extra lines with zero quantities are superfluous. Start by setting up a "skeleton" or "master" spreadsheet that has all 80 items, one per line, with descriptions and prices, but with no quantities. The first few lines of the master invoice might look like this: 89 - NOPRINT A B C D E 1 INVOICE 2 Date: 3 Sold to: 4 5 6 Qty Part# Description Price Extended 7 1001 Printer Stand 95.00 A7*D7 8 1002 Paper Tray 40.00 A8*D8 9 1003 Tractor Feed Option 125.00 A9*D9 10 1004 Ribbon Cartridge 9.95 A10*D10 (etc.) The invoice spreadsheet contains a line for each of eighty products, with their descriptions, prices, part number, and any other information we want. The quantities are empty, so they can be filled in later. Column E has the extended amount; it is a formula with the quantity times the price. In a real invoice application, there is usually quite a bit more other info at the top, such as sold to, ship to, PO number, date, invoice number, etc. At the bottom there are invoice totals, sales tax, shipping charges, invoice discounts, etc. most of which can be computed by CALC. Now, back to the NOPRINT function. How do we keep lines from printing that have no quantities? It's easy. Go to cell F7 and enter: IF(A7=0,NOPRINT) Or you may prefer the alternate form of the IF function: IF A7 = 0 THEN NOPRINT Now use the /Replicate command to copy the formulas in E7 and F7 all the way down to the last product in line 86. Note that [NOPRINT] appears in all the cells in 90 - NOPRINT column F, because all the quantities are zero. As soon as a quantity is entered into column A (and recalculation is done), the [NOPRINT] on that line becomes .00 and the line now prints. Since we don't want .00's printing on our invoice, use the /Format, Zero-blank command to hide them. Or change the width of column F to zero, which also hides them. Before entering any quantities, set recalculation to manual with /GM and save the spreadsheet, giving it a name like INVOICE.MAS. It is now the master invoice spreadsheet. Later, to prepare an invoice, load the master spreadsheet, type in the quantities, and /Print. If you want to save the new invoice, give it a unique name (such as the invoice number, or customer name) As an added sophistication, you may want to protect all the fields on the spreadsheet except the quantity column and shipping info, so only they can be entered. It keeps other fields from being accidentally altered, and it also allows you to clear all the unprotected fields with the /Blank,ALL command, which does not clear protected fields. 91 - NPV ____________________________________________________________________ NPV Function Purpose: Computes the net present value of a stream of flows, at a specified discount rate, and returns the result to the current cell or formula. Format: NPV(.12,A1:A20) Rate specified as value NPV(A1,B1:E1) Rate in another cell NPV(A1/100,B1:E1) Rate specified as formula Remarks: The first operand of the NPV function is the discount rate. It may be a value, a cell reference, or a formula. The interval between the stream of flows must be constant, and determined by the rate; i.e. if the flows are annual, use an annual discount rate. If the flows are monthly, use a monthly discount rate. The second operand of the NPV function is a range of contiguous cells, which contains the stream of flows. Example: A client has an annual income of $40,000 and he expects it to increase 5% annually. Make a spreadsheet showing his annual income for the next ten years, total income for ten years, and net present value of the total income, assuming an annual discount rate of 8%: A1: 40000 A2: A1*1.05 A3-A10: /R A2 A3:A10 (adjust - Y) A12: SUM(A1:A10) A13: NPV(.08,A1:A10) Cell A13 will contain the net present value of the ten years' income. For larger spreadsheets using NPV several times, it is a good idea to put the discount in another 92 - NPV cell, so it can be easily changed without having to enter the formulas again. In the example above, make these two changes: B1: .08 A13: NPV(B1,A1:A10) 93 - PAGE ____________________________________________________________________ PAGE Function Purpose: Advances a printed spreadsheet to a new page. Optional operand allows conditional page break. Format: PAGE Advances to next page after printing current line PAGE(3) Advances to next page if there are fewer than three lines left on the current page Remarks: When PAGE is entered into a cell, it displays as [PAGE] on the screen. When PAGE(n) is entered, [PAGE(n)] is displayed on the screen. The value n must be a positive integer between 0 and 99. It cannot be a cell reference or formula. When a spreadsheet is printed, a line count is kept internally, to count the number of lines printed on a page. When this line counter reaches its maximum (the "lines per page" value specified in /Print), a "page break" occurs. At page break time, the following things happen: . If footings are specified, paper is advanced to the footing area, and footings are printed . A form feed is sent to the printer, to advance to top of page . If Pause option is specified, a message is given to press ENTER to print the next page . Line counter is reset to zero 94 - PAGE . If Border option is specified, top borders are printed . If headings are specified, they are printed, and the line counter is incremented accordingly When PAGE is encountered during printing, the line it appears in is printed first, then the steps listed above are followed. When PAGE(n) is encountered during printing, the line it appears in is printed first, then the current line count is compared to n. If there is not enough room on the page for n more lines, the page break steps listed above are followed. If there is enough room, the PAGE(n) function is ignored. Note that PAGE(n) refers to the next n lines. The current line PAGE or PAGE(n) appears in is printed on the current page. PAGE and PAGE(n) can be the second or third operands in an IF function, allowing page breaks to be even further controlled by data in the spreadsheet. The PAGE function cannot be used in a formula. 95 - PAGE Example: A spreadsheet lists an employee, then has five lines of information about that employee. If the bottom of the page is approaching, we don't want to print only one or two lines of data on the current page, then print the last four or five lines on the next page. By adding a PAGE(5) function ahead of each employee, all five of their lines will always appear on the same page. The screen might look like this: A B C 1 [PAGE(5)] 2 Employee A - line 1 3 line 2 4 line 3 5 line 4 6 line 5 7 [PAGE(5)] 8 Employee B - line 1 9 - line 2 (etc.) 96 - PAYMENT ____________________________________________________________________ PAYMENT Function Purpose: Computes the payment amount per period for a given principal amount, rate and number of periods. (See also PRINCIPAL, RATE and PERIODS functions.) Format: PAYMENT(principal,rate,periods) PAYMENT(1000,.01,12) Returns 88.85 PAYMENT(A1,.18/12,36) If A1 is 1000, returns 36.16 PAYMENT(B1,100,A1*2/A5) Resolves the formula, then computes the payment Remarks: PAYMENT(x,y,z) can be entered into a cell, or the PAYMENT function can be used in a formula. All three operands must be specified. The rate operand is the rate per period. The period length needs to be consistent; i.e. in the first example above, the term is twelve months, so the rate is a monthly rate, and the payment amount returned is the payment per month. One note on amortizations: as the truth-in-lending laws so vividly indicate, there are many ways to amortize. If CALC comes up with a different answer than your bank, it may be because of their compounding method, or because of rounding. Generally, CALC's calculation method is mathematically sound, and yields the same result as an interest amortization table. Example: A local used car dealer offers a time-payment plan. A $5,000 car requires no down payment, and requires 36 monthly payments at 18% annual interest. Compute the 97 - PAYMENT monthly payment. In any cell, enter: PAYMENT(5000,.18/12,36) The cell displays 180.77, which is the monthly payment. The interest rate of .18 was an annual rate, so we used .18/12 for the monthly rate. 98 - PERIODS ____________________________________________________________________ PERIODS Function Purpose: Computes the term of a loan (number of periods) for a given principal amount, payment and interest rate. (See also PRINCIPAL, PAYMENT and RATE functions.) Format: PERIODS(principal,payment,rate) PERIODS(1000,88.85,.01) Returns 12 PERIODS(A1,36.16,.18/12) If A1 is 1000, returns 36 PERIODS(B1,100,A1*2/A5) Resolves the formula, then computes the periods Remarks: PERIODS(x,y,z) can be entered into a cell, or the PERIODS function can be used in a formula. TERM is an alternate name for the PERIODS function. All three operands must be specified. The rate operand is the rate per period. The payment and rate need to be consistent; i.e. in the first example above, the rate is a monthly rate, and the payment amount is monthly, so the number returned is the number of months. One note on amortizations: as the truth-in-lending laws so vividly indicate, there are many ways to amortize. If CALC comes up with a different answer than your bank, it may be because of their compounding method, or because of rounding. Generally, CALC's calculation method is mathematically sound, and yields the same result as an interest amortization table. 99 - PERIODS Example: A local used car dealer offers a time-payment plan. A $5,000 car requires no down payment, and has monthly payments of 180.77 at 18% annual interest. Compute the number of months of payments. In any cell, enter: PERIODS(5000,180.77,.18/12) The cell displays 36, which is the number of months. The interest rate of .18 was an annual rate, so we used .18/12 for the monthly rate. 100